/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package jdh;

import javax.swing.table.AbstractTableModel;
import java.sql.*;

/**
 *
 * @author tale
 */
public class DataModel extends AbstractTableModel{
    static Backend backend = Backend.getDefaultBackend();
    Statement statement, ust = null;
    ResultSet rs = null;
    ResultSetMetaData md = null;
    String select = "";
    String selectTemplate = "";
    String updateTemplate = "";
    String insertTemplate = "";
    String deleteTemplate = "";
    int column = 0;
    int row = 0;
    boolean hasData = false;
    
    public DataModel(){
        this("", "", "", "");
    }
    
    public DataModel(String select){
        this(select, "", "", "");
    }
    public DataModel(String select, String update, String insert, String delete){
        this(select, update, insert, delete, "");
    }

    public DataModel(String select, String update, String insert, String delete, String template){
        this.select = select;
        updateTemplate = update;
        insertTemplate = insert;
        deleteTemplate = delete;
        selectTemplate = template;
        try{
            statement = backend.getConnection().createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        }catch(Exception e){
            System.out.println("DataModel(): " + e.toString());
        }

        if (refresh() == false)
            System.out.println("DataModel() initial failed.");
    }

    public static void setBackend(Backend server){
        backend = server;
    }

    public void setSelect(String select){
        this.select = select;
        refresh();
    }

    public void setSelectID(String id){
        select = String.format(selectTemplate, id);
        refresh();
    }

    public boolean refresh(){
        hasData = false;

        int row = 0;
        int column = 0;
        if (select.compareTo("") == 0) return true;
        System.out.println("DataModel:" + select);

        try {
            rs = statement.executeQuery(select);
            md = rs.getMetaData();
            column = md.getColumnCount();
            if (rs.last()){
                row = rs.getRow();
                rs.beforeFirst();
            }
            hasData = true;
        }catch(SQLException ex) {
            System.out.println(ex.toString());
        }catch(NullPointerException ex){
            System.out.println(ex.toString());
        }finally{
            if (this.row != row || this.column != column){
                this.row = row;
                this.column = column;
                fireTableStructureChanged();
            }
            else
                fireTableDataChanged();            
            System.out.println("Column = " + column + "\tRow = " + row);
        }
        return hasData;
    }

    public int getRowCount() {
        //System.out.println("DataModel.getRowCount = " + row);
        return row;
    }

    public int getColumnCount() {
        //System.out.println("DataModel.getColumnCount = " + column);
        return column;
    }

    @Override
    public String getColumnName(int columnIndex) {
        if (hasData == false)
            return "ColumnName";
        try {
            return md.getColumnLabel(columnIndex + 1);
        } catch (SQLException ex) {
            System.out.println(ex.toString());
            return "Column " + columnIndex;
        }
    }

    @Override
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return true;
    }

    public Object getValueAt(int rowIndex, int columnIndex) {
        if (hasData == false)
            return "No data.";
        String value = "";
        try{
            rs.beforeFirst();
            while (rs.next()){
                if (rs.getRow() == rowIndex + 1){
                    value = rs.getString(columnIndex + 1);
                    if (value == null) return "";
                    else return value;
                }
            }
        }catch(SQLException ex){
            System.out.println(" = (exception)");
            return value;
        }
        return value;
    }

    @Override
    public void setValueAt(Object aValue, int rowIndex, int columnIndex) {
        String oldValue = "";
//        try{
//            oldValue = getValueAt(rowIndex, columnIndex).toString();
//        }catch(Exception e){
//            System.out.println("(" + rowIndex + "," + column + ") : " + e.toString());
//        }
        String id = getValueAt(rowIndex, findColumn("ID")).toString();
        String newValue = aValue.toString();
        String columnName =  getColumnName(columnIndex);
        if (updateTemplate.compareTo("") == 0) return;
        String update = String.format(updateTemplate + " WHERE ID = '%S'", id);
        update = update.replace("{NEW}", newValue).replace("{COLUMN}", columnName);
        System.out.println("DataModel:" + update);
        try{
            if (ust == null)
                ust = backend.getConnection().createStatement();
            ust.executeUpdate(update);
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }finally{
            this.refresh();
        }
    }

    public int delete(int rowIndex){
        int valuedRow = 0;
        if (deleteTemplate.compareTo("") == 0) return 0;
        String id = getValueAt(rowIndex, findColumn("ID")).toString();
        String delete = String.format(deleteTemplate + " WHERE ID = '%S' ", id);
        System.out.println("SQL: " + delete);
        try{
            if (ust == null)
                ust = backend.getConnection().createStatement();
            valuedRow = ust.executeUpdate(delete);
        } catch (SQLException ex) {
            System.out.println(ex.toString());
        }finally{
            this.refresh();
            return valuedRow;
        }
    }

    public int insert(){
        int valuedRow = 0;
        String insert = insertTemplate;
        if (insert.compareTo("") == 0) return 0;
        System.out.println("SQL: " + insert);
        try{
            if (ust == null)
                ust = backend.getConnection().createStatement();
            valuedRow = ust.executeUpdate(insert);
        }catch(SQLException e){
            System.out.println(e.toString());
        }finally{
            this.refresh();
            return valuedRow;
        }
    }

    public static DataModel getPlanModel(){
        return new DataModel("SELECT * FROM PLANS WHERE ID != '1' ORDER BY ID",
                //"UPDATE PLANS SET {COLUMN} = '{NEW}' WHERE {COLUMN} = '{OLD}' ",
                "UPDATE PLANS SET {COLUMN} = '{NEW}' ",
                "INSERT INTO PLANS(NAME) VALUES('NO NAME')",
                "DELETE FROM PLANS ");
    }

    // 返回导师课表模型
    public static DataModel getTeacherModel(String id){
        String select = "SELECT PLANS.NAME PLANS, " +
                "LECTURES.NAME LECTURE, " +
                "COUNT(LEARNINGS.STUDENTID) STUDENTS " +
                "FROM CLASSES, LECTURES, PLANS, LEARNINGS " +
                "WHERE CLASSES.LECTUREID = LECTURES.ID " +
                "AND CLASSES.PLANID = LEARNINGS.PLANID " +
                "AND CLASSES.PLANID = PLANS.ID " +
                "AND CLASSES.TEACHERID = '%S' " +
                "GROUP BY PLANS.NAME, LECTURES.NAME ";
        return new DataModel(String.format(select, id));
    }

    public static DataModel getStudentModel(String id){
        String select = "SELECT PLANS.NAME PLANS, " +
                "LECTURES.NAME LECTURE, " +
                "TEACHERS.NAME TEACHER, " +
                "CLASSES.TIME, " +
                "CLASSES.ADDRESS " +
                "FROM PLANS, CLASSES, LECTURES, TEACHERS " +
                "WHERE CLASSES.PLANID = PLANS.ID " +
                "AND CLASSES.LECTUREID = LECTURES.ID " +
                "AND CLASSES.TEACHERID = TEACHERS.ID " +
                "AND PLANS.ID IN (" +
                "SELECT PLANID FROM LEARNINGS " +
                "WHERE LEARNINGS.STUDENTID = '%S')" +
                "ORDER BY PLANS.NAME, LECTURES.NAME ";
        return new DataModel(String.format(select, id));
    }

    public static DataModel getClassModel(){
        //String select = "SELECT * FROM CLASSES";
        String select = "SELECT PLANS.NAME PLANS, " +
                "LECTURES.NAME LECTURE, " +
                "TEACHERS.NAME TEACHER, " +
                "CLASSES.TIME, CLASSES.ADDRESS " +
                "FROM CLASSES, PLANS, LECTURES, TEACHERS " +
                "WHERE CLASSES.PLANID = PLANS.ID " +
                "AND CLASSES.LECTUREID = LECTURES.ID " +
                "AND CLASSES.TEACHERID = TEACHERS.ID ";
        String delete = "";// "DELETE FROM CLASSES WHERE ";
        return new DataModel(select, "", "", delete);
    }

    public static DataModel getClassInfoModel(String lecture, String teacher){
        String select = "SELECT DISTINCT STUDENTS.NAME STUDENT " +
                "FROM CLASSES, STUDENTS, LEARNINGS " +
                "WHERE CLASSES.PLANID = LEARNINGS.PLANID " +
                "AND CLASSES.LECTUREID = '%S' " +
                "AND CLASSES.TEACHERID = '%S' " +
                "AND LEARNINGS.STUDENTID = LEARNINGS.STUDENTID ";
        return new DataModel(String.format(select, lecture, teacher));
    }

    public static DataModel getLectureModel(){
        String select = "SELECT * FROM LECTURES";
        String update = "UPDATE LECTURES SET {COLUMN} = '{NEW}' ";
        String insert = "INSERT INTO LECTURES(NAME) VALUES('NEW LECTURE')";
        String delete = "DELETE FROM LECTURES ";
        return new DataModel(select, update, insert, delete);
    }

    public static DataModel getMarkFromStudentModel(String id){
        String select = String.format("SELECT LECTURES.NAME LECTURE, " +
                "TEACHERS.NAME TEACHER, " +
                "MARKS.STUDENTMARK MARK, " +
                "MARKS.TEACHERMARK 'TEACHER MARK' " +
                "FROM MARKS, LECTURES, TEACHERS " +
                "WHERE MARKS.TEACHERID = TEACHERS.ID " +
                "AND MARKS.LECTUREID = LECTURES.ID " +
                "AND MARKS.STUDENTID = '%S' ", id);
        return new DataModel(select, "", "", "");
    }

    public static DataModel getMarkFromTeacherModel(String id){
        String select = String.format("SELECT LECTURES.NAME LECTURE, " +
                "STUDENTS.NAME TEACHER, " +
                "MARKS.TEACHERMARK MARK, " +
                "MARKS.STUDENTMARK 'STUDETNS MARK' " +
                "FROM MARKS, LECTURES, STUDENTS " +
                "WHERE MARKS.STUDENTID = STUDENTS.ID " +
                "AND MARKS.LECTUREID = LECTURES.ID " +
                "AND MARKS.TEACHERID = '%S'", id);
        return new DataModel(select, "", "", "");
    }

    public static DataModel getRegisterModel(String id){
        String select = String.format("SELECT PLANS.NAME PLAN, " +
                "LEARNINGS.REGISTER " +
                "FROM PLANS, LEARNINGS " +
                "WHERE LEARNINGS.PLANID = PLANS.ID " +
                "AND LEARNINGS.STUDENTID = '%S'", id);
        return new DataModel(select, "", "", "");
    }
    
    public static DataModel getClassInModel(){
        String classInTemplate = "SELECT LECTURES.NAME LECTURE, " +
                "TEACHERS.NAME TEACHER " +
                "FROM CLASSES, LECTURES, TEACHERS" +
                " WHERE CLASSES.LECTUREID = LECTURES.ID" +
                " AND CLASSES.TEACHERID = TEACHERS.ID" +
                " AND CLASSES.PLANID = '%s'";
        return new DataModel("", "", "", "", classInTemplate);
    }

    public static DataModel getClassOutMode(){
        String classOutTemplate = "SELECT LECTURES.NAME LECTURE, " +
                "TEACHERS.NAME TEACHER" +
                " FROM CLASSES, LECTURES, TEACHERS" +
                " WHERE CLASSES.LECTUREID = LECTURES.ID" +
                " AND CLASSES.TEACHERID = TEACHERS.ID" +
                " AND CLASSES.PLANID = '1'";
        return new DataModel("", "", "", "", classOutTemplate);
    }

    private static DataModel getUserModel(String table){
        String select = String.format("SELECT * FROM %s", table);
        String update = String.format("UPDATE %s SET {COLUMN} = '{NEW}' ", table);
        String insert = String.format("INSERT INTO %s(NAME, PASSWORD) " +
                "VALUES('NO NAME', '000000')", table);
        String delete = String.format( "DELETE FROM %s ", table);
        return new DataModel(select, update, insert, delete);
    }

    public static DataModel getUserAdminModel(){
        return getUserModel("ADMINS");
    }

    public static DataModel getUserTeacherModel(){
        return getUserModel("TEACHERS");
    }

    public static DataModel getUserStudentModel(){
        return getUserModel("STUDENTS");
    }

    public static void main(String[] args){
        DataModel.getPlanModel();
        DataModel.getTeacherModel("1");
        DataModel.getStudentModel("1");
        DataModel.getClassModel();
        DataModel.getLectureModel();
        DataModel.getMarkFromStudentModel("1");
        DataModel.getMarkFromTeacherModel("1");
        DataModel.getRegisterModel("1");
        DataModel.getClassInModel();
        DataModel.getClassOutMode();
        DataModel.getUserModel("ADMINS");
        DataModel.getClassInfoModel("1", "1");
    }


}
